<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
class zExam{
    
    public $conn;
    
    public function __construct() {
        $this->conn = Yii::app()->db;
    }
    
    //(高中)分数段功能
    public function _getExam($where=''){
        
        $sql = ' SELECT "exam_id","exam_name","exam_term_name" FROM "{{exam}}" ';
        $sql .= $where;
        $result = $this->conn->createCommand($sql);
        $data = $result->queryAll();
        
        return $data;
    }
    
    public function _getExamObject($exam_list='',$role_id=0){
        
        $new_data = array();
        
        $where = '';
        if($role_id != 1 && !empty($exam_list)){
            $where = ' AND "exam_id" IN ('.$exam_list.')';
        }
        $sql = ' SELECT "exam_id","exam_name","exam_term_name" FROM "{{exam}}" WHERE "is_del" = 0  '.$where.' AND "exam_status" != 0 ORDER BY "time_created" DESC ';

        $result = $this->conn->createCommand($sql);
        $data = $result->queryAll();
        if(!empty($data)){
            foreach($data as $value){
                $new_data[$value['exam_id']] = $value;
            }
        }
        
        return $new_data;
    }
    
    public function _getExamInfo($exam_id){
        
        $sql = ' SELECT "exam_id","exam_name","exam_term_name","exam_subject_ids" FROM "{{exam}}" WHERE "exam_id" = :exam_id ';

        $result = $this->conn->createCommand($sql);
        $result->bindParam(':exam_id',$exam_id,PDO::PARAM_STR);
        $data = $result->queryRow();
        
        return $data;
    }
    
    /**
     * 得到分数线
     * 
     * @version 1.0
     * @date 2013-12-17
     * @author jinyue.wang
     */
    public function _getEduScore($sid){
        
        $sql = 'SELECT "es_id","exam_id","disciplin_type","score_type1","score_type2","score_type3" FROM "{{edu_score}}" ';
        $sql .= ' WHERE "es_id" = :sid ';
        
        $result = $this->conn->createCommand($sql);
        $result->bindParam(':sid', $sid,PDO::PARAM_STR);
        $data = $result->queryRow();
        
        return $data;
    }
    
    public function _getEduScoreArray($exam_id){
        
        $new_data = array();
        
        $sql = 'SELECT "es_id","exam_id","disciplin_type","score_type1","score_type2","score_type3" FROM "{{edu_score}}" ';
        $sql .= ' WHERE "exam_id" = :sid ';
        
        $result = $this->conn->createCommand($sql);
        $result->bindParam(':sid', $exam_id,PDO::PARAM_STR);
        $data = $result->queryAll();
        if(!empty($data)){
            foreach ($data as $value){
                $new_data[$value['disciplin_type']]  = $value;
            }
        }
        return $new_data;
        
    }
    
    public function _updEduScore($sid,$data){
        
        $status = 0;
        
        if($sid>0){

            $sql = 'UPDATE "{{edu_score}}" SET "update_user" = :update_user,"update_time" = :update_time,"exam_id" = :exam_id,"disciplin_type" = :type,"score_type1" = :stype1,"score_type2" = :stype2,"score_type3" = :stype3 ';
            $sql .= 'WHERE "es_id" = :sid';
            $result = $this->conn->createCommand($sql);
            
            $result->bindParam(':exam_id',$data['exam_id'],PDO::PARAM_STR);
            $result->bindParam(':type',$data['type'],PDO::PARAM_STR);
            $result->bindParam(':stype1',$data['stype1'],PDO::PARAM_STR);
            $result->bindParam(':stype2',$data['stype2'],PDO::PARAM_STR);
            $result->bindParam(':stype3',$data['stype3'],PDO::PARAM_STR);
            $result->bindParam(':sid',$data['sid'],PDO::PARAM_STR);
       
            $result->bindParam(':update_user', $data['update_user'], PDO::PARAM_STR);
            $result->bindParam(':update_time', $data['update_time'], PDO::PARAM_STR);
            
            $status = $result->execute();
            
        }else{
            $sql = 'INSERT INTO "{{edu_score}}" ("exam_id","disciplin_type","score_type1","score_type2","score_type3","create_user","create_time","update_user","update_time") ';
            $sql .= ' VALUES (:exam_id,:type,:stype1,:stype2,:stype3,:create_user,:create_time,:update_user,:update_time) ';
            $result = $this->conn->createCommand($sql);
            
            $result->bindParam(':exam_id',$data['exam_id'],PDO::PARAM_STR);
            $result->bindParam(':type',$data['type'],PDO::PARAM_STR);
            $result->bindParam(':stype1',$data['stype1'],PDO::PARAM_STR);
            $result->bindParam(':stype2',$data['stype2'],PDO::PARAM_STR);
            $result->bindParam(':stype3',$data['stype3'],PDO::PARAM_STR);
            $result->bindParam(':create_user', $data['create_user'], PDO::PARAM_STR);
            $result->bindParam(':create_time', $data['create_time'], PDO::PARAM_STR);
            $result->bindParam(':update_user', $data['update_user'], PDO::PARAM_STR);
            $result->bindParam(':update_time', $data['update_time'], PDO::PARAM_STR);
            $status = $result->execute();
        }
        
        return $status;
    }
    
    //(初中)统计指标功能
    public function _getSubjectScore($sid){
        
        $sql = 'SELECT "es_id","exam_id","subject_id","score_type1","score_type2","score_type3" FROM "{{subject_score}}" ';
        $sql .= ' WHERE "es_id" = :sid ';
        
        $result = $this->conn->createCommand($sql);
        $result->bindParam(':sid', $sid,PDO::PARAM_STR);
        $data = $result->queryRow();
        
        return $data;
        
    }
    
    public function _getSubArray($exam_id){
        
        $new_data = array();
        
        $sql = 'SELECT "es_id","exam_id","subject_id","score_type1","score_type2","score_type3" FROM "{{subject_score}}" ';
        $sql .= ' WHERE "exam_id" = :eid ';
        
        $result = $this->conn->createCommand($sql);
        $result->bindParam(':eid', $exam_id,PDO::PARAM_STR);
        $data = $result->queryAll();
        if(!empty($data)){
            foreach($data as $value){
                $new_data[$value['subject_id']] = $value;
            }
        }
        
        return $new_data;
    }
 
    /**
     * 得到统计指标
     * 
     * @param $exam_id
     * 
     * @return array
     * 
     * @version 1.0
     * @date 2014-01-01
     * @author jinyue.wang
     */
    public function _getCzSubject($exam_id) {
        $new_data = $return_data = array();

        $allData = array(
            'score_type1' => 0,
            'score_type2' => 0,
            'score_type3' => 0
        );

        $sql = 'SELECT "es_id","exam_id","subject_id","score_type1","score_type2","score_type3" FROM "{{subject_score}}" ';
        $sql .= ' WHERE "exam_id" = :eid ';

        $result = $this->conn->createCommand($sql);
        $result->bindParam(':eid', $exam_id, PDO::PARAM_STR);
        $data = $result->queryAll();
        if (!empty($data)) {

            foreach ($data as $value) {
                $new_data[$value['subject_id']] = $value;
                $allData['score_type1'] += $value['score_type1'];
                $allData['score_type2'] += $value['score_type2'];
                $allData['score_type3'] += $value['score_type3'];
            }
            $return_data['subject'] = $new_data;
            $return_data['point'] = $allData;
        }

        return $return_data;
    }
    
    public function _updSubjectScore($sid,$data){
        
        $status = 0;
        
        if($sid>0){

            $sql = 'UPDATE "{{subject_score}}" SET "update_user" = :update_user,"update_time" = :update_time,"exam_id" = :exam_id,"subject_id" = :subject,"score_type1" = :stype1,"score_type2" = :stype2,"score_type3" = :stype3 ';
            $sql .= 'WHERE "es_id" = :sid ';
            $result = $this->conn->createCommand($sql);
            
            $result->bindParam(':exam_id',$data['exam_id'],PDO::PARAM_STR);
            $result->bindParam(':subject',$data['subject'],PDO::PARAM_STR);
            $result->bindParam(':stype1',$data['stype1'],PDO::PARAM_STR);
            $result->bindParam(':stype2',$data['stype2'],PDO::PARAM_STR);
            $result->bindParam(':stype3',$data['stype3'],PDO::PARAM_STR);
            $result->bindParam(':sid',$data['sid'],PDO::PARAM_STR);
            $result->bindParam(':update_user', $data['update_user'], PDO::PARAM_STR);
            $result->bindParam(':update_time', $data['update_time'], PDO::PARAM_STR);
            $status = $result->execute();
            
        }else{
            
            $sql = 'INSERT INTO "{{subject_score}}" ("exam_id","subject_id","score_type1","score_type2","score_type3","create_user","create_time","update_user","update_time") ';
            $sql .= " VALUES (:exam_id,:subject,:stype1,:stype2,:stype3,:create_user,:create_time,:update_user,:update_time) ";
            $result = $this->conn->createCommand($sql);
            
            $result->bindParam(':exam_id',$data['exam_id'],PDO::PARAM_STR);
            $result->bindParam(':subject',$data['subject'],PDO::PARAM_STR);
            $result->bindParam(':stype1',$data['stype1'],PDO::PARAM_STR);
            $result->bindParam(':stype2',$data['stype2'],PDO::PARAM_STR);
            $result->bindParam(':stype3',$data['stype3'],PDO::PARAM_STR);
            $result->bindParam(':create_user', $data['create_user'], PDO::PARAM_STR);
            $result->bindParam(':create_time', $data['create_time'], PDO::PARAM_STR);
            $result->bindParam(':update_user', $data['update_user'], PDO::PARAM_STR);
            $result->bindParam(':update_time', $data['update_time'], PDO::PARAM_STR);
            $status = $result->execute();
        }
        
        return $status;
    }
    
    //(初中)统计分数段
    public function _getSectionScore($sid) {
        
        $sql = 'SELECT "es_id","exam_id","score_type1","score_type2","score_type3" FROM "{{score_section}}" ';
        $sql .= ' WHERE "es_id" = :sid ';

        $result = $this->conn->createCommand($sql);
        $result->bindParam(':sid', $sid, PDO::PARAM_STR);
        $data = $result->queryRow();

        return $data;
    }
    
    public function _getSectionObject($exam_id) {
        
        $sql = 'SELECT "es_id","exam_id","score_type1","score_type2","score_type3" FROM "{{score_section}}" ';
        $sql .= ' WHERE "exam_id" = :sid ';

        $result = $this->conn->createCommand($sql);
        $result->bindParam(':sid', $exam_id, PDO::PARAM_STR);
        $data = $result->queryRow();

        return $data;
    }
    
    
    public function _updSectionScore($sid,$data){
        
        $status = 0;
        
        if($sid>0){

            $sql = 'UPDATE "{{score_section}}" SET "update_user"=:update_user,"update_time"=:update_time,"exam_id" = :exam_id,"score_type1" = :stype1,"score_type2" = :stype2,"score_type3" = :stype3 ';
            $sql .= 'WHERE "es_id" = :sid';
            $result = $this->conn->createCommand($sql);
            
            $result->bindParam(':exam_id',$data['exam_id'],PDO::PARAM_STR);
            $result->bindParam(':stype1',$data['stype1'],PDO::PARAM_STR);
            $result->bindParam(':stype2',$data['stype2'],PDO::PARAM_STR);
            $result->bindParam(':stype3',$data['stype3'],PDO::PARAM_STR);
            $result->bindParam(':sid',$data['sid'],PDO::PARAM_STR);
            $result->bindParam(':update_user', $data['update_user'], PDO::PARAM_STR);
            $result->bindParam(':update_time', $data['update_time'], PDO::PARAM_STR);
            $status = $result->execute();
            
        }else{
            
            $sql = 'INSERT INTO "{{score_section}}" ("exam_id","score_type1","score_type2","score_type3","create_user","create_time","update_user","update_time") ';
            $sql .= ' VALUES (:exam_id,:stype1,:stype2,:stype3,:create_user,:create_time,:update_user,:update_time) ';
            $result = $this->conn->createCommand($sql);
            
            $result->bindParam(':exam_id',$data['exam_id'],PDO::PARAM_STR);
            $result->bindParam(':stype1',$data['stype1'],PDO::PARAM_STR);
            $result->bindParam(':stype2',$data['stype2'],PDO::PARAM_STR);
            $result->bindParam(':stype3',$data['stype3'],PDO::PARAM_STR);
            $result->bindParam(':create_user', $data['create_user'], PDO::PARAM_STR);
            $result->bindParam(':create_time', $data['create_time'], PDO::PARAM_STR);
            $result->bindParam(':update_user', $data['update_user'], PDO::PARAM_STR);
            $result->bindParam(':update_time', $data['update_time'], PDO::PARAM_STR);
            
            $status = $result->execute();
        }
        
        return $status;
    }
    
    /**
     * 根据考试学校得到考试
     * 
     * @param $school_id
     * @param $exam_id
     * 
     * @return array
     * 
     * @version 1.0
     * @author jinyue.wang
     * @date 2013-12-15
     */
    public function _getExamArray($school_id,$exam_id,$exam_array){
        
        if(empty($exam_id))
            return null;
        
        $new_data = array();
        
        $sql = 'SELECT "exam_id","school_id","student_id","subject_id","disciplin_type","score" FROM "{{point}}" WHERE "exam_id" = :exam_id ';
        if($school_id>0){
            $sql .= ' AND "school_id" = '.$school_id;
        }

        $result = $this->conn->createCommand($sql);
        $result->bindParam(':exam_id',$exam_id,PDO::PARAM_STR);
        
        $data = $result->queryAll();
        if(!empty($data)){
            
            $type1 = $type2 = $type3 = 0;
            $student_array = array();
            foreach($data as $value){
                
                //总分统计
                if(!isset($new_data[$value['school_id']]))
                    $new_data[$value['school_id']]  = array();
                
                if(!isset($new_data[$value['school_id']]['people'])){
                    $new_data[$value['school_id']]['people'] = 0;
                }
                
                if(!isset($new_data[$value['school_id']]['point']))
                    $new_data[$value['school_id']]['point'] = 0;
                
                if(!isset($student_array[$value['student_id']])){
                    $new_data[$value['school_id']]['people'] += 1;   //总人数
                    $student_array[$value['student_id']] = 1;
                }
                
                $new_data[$value['school_id']]['point'] += $value['score']; //总分数
                
                //单科总分
                if (!isset($new_data[$value['school_id']]['student'])) {
                    $new_data[$value['school_id']]['student'] = array();
                }
                if (!isset($new_data[$value['school_id']]['student'][$value['subject_id']])) {
                    $new_data[$value['school_id']]['student'][$value['subject_id']] = 0;
                }
                
                $new_data[$value['school_id']]['student'][$value['subject_id']] += $value['score'];  //单科总分
                
                $type = self::_getSectionPoint($value['school_id'],$value['subject_id'],$value['score'],$exam_array);
                if(!isset($new_data[$value['school_id']]['subject'])){
                    $new_data[$value['school_id']]['subject'] = array();
                }
                if(!isset($new_data[$value['school_id']]['subject'][$value['subject_id']][$type])){
                    $new_data[$value['school_id']]['subject'][$value['subject_id']][$type] = 0;
                }
                $new_data[$value['school_id']]['subject'][$value['subject_id']][$type] +=1;
                
            }
        }

        return $new_data;
    }
    
    /**
     * 判断当前科目分数分布
     * 
     * @param $school_id
     * @param $subject_id
     * @param $exam_array
     * 
     * @author jinyue.wang
     * @version 1.0
     * @date 2013-12-15
     */
    public function _getSectionPoint($school_id,$subject_id,$point,$exam_array){
        
        if(empty($school_id) || empty($subject_id) || empty($exam_array))
            return null;
        
        $data = array();
        
        //单科总分
        if (!isset($data[$school_id]['subject'])) {
            $data[$school_id]['subject'] = array();
        }

        if (!isset($data[$school_id]['subject'][$subject_id])) {
            $data[$school_id]['subject'][$subject_id] = array();
        }
        
        $people = 0;
        if(isset($exam_array[$subject_id])){
            if($point>=$exam_array[$subject_id]['score_type1']){
                $people = 1;
            }else{
                if($point>=$exam_array[$subject_id]['score_type2']){
                    $people =2;
                }else{
                    if($point<$exam_array[$subject_id]['score_type1']){
                        $people =3;
                    }
                }
            }
        }
        
        return $people;
    }
    
    /**
     * 分段统计结果
     * 
     * @param $school_id
     * @param $exam_id
     * 
     * @return array
     * 
     * @author jinyue.wang
     * @date 2013-12-15
     * @version 1.0
     */
    public function _getExamArrayList($school_id,$exam_id,$scoreArray,$school_ids=''){
        
        if(empty($exam_id))
            return null;
        
        $new_data = array();
        
        $sql = 'SELECT "exam_id","school_id","student_id","socre","ywf","sxf","yyf","wlf","hxf","swf","dlf","lsf","zzf" FROM "{{student}}" WHERE "exam_id" = :exam_id AND "statis" = \'T\' ';
        if($school_id>0){
            $sql .= ' AND "school_id" = '.$school_id;
        }else{
        	if(!empty($school_ids)){
        		$sql .= ' AND "school_id" IN ('.$school_ids.')';
        	}
        }

        $result = $this->conn->createCommand($sql);
        $result->bindParam(':exam_id',$exam_id,PDO::PARAM_STR);
        
        $data = $result->queryAll();
        if(!empty($data)){
            foreach($data as $value){
                $zfk = $value['ywf']+$value['sxf']+$value['yyf']+$value['wlf']+$value['hxf']+$value['swf']+$value['lsf']+$value['dlf']+$value['swf'];
                $new_data[$value['school_id']][$value['student_id']] = $zfk;
            }
        }

        $d = $k = $dr = $return_data = array();
        //循环判断
        if(!empty($scoreArray)){
            foreach ($scoreArray as $sVal){
                $k[] = $sVal['name'];
                
                foreach($new_data as $nKey => $nVal){
                    $d[$nKey]['people'] = count($nVal);  //学校人数
                    foreach($nVal as $ks => $val){
                        $show = 0;
                        if(!empty($student_array)){
                            if(in_array($ks, $student_array)){
                                $show = 1;
                            }
                        }else{
                            $show = 1;
                        }
                        if ($show == 1) {
                        $point = (float)$val;
                        if(!isset($return_data[$nKey][$sVal['name']])){
                            $return_data[$nKey][$sVal['name']] = 0;
                        }
                        if($point>=$sVal['data'][1] && $point<=$sVal['data'][0]){ 
                            $return_data[$nKey][$sVal['name']] += 1;
                        }
                    }
                    }
                    $d[$nKey]['data'] = isset($return_data[$nKey])?$return_data[$nKey]:array(); 
                }  
            }
            $dr['key'] = $k;
            $dr['data'] = $d;
        }

        return $dr;
    }
    
    //区外统计对比
    public function _getOutsideArray($exam_id,$point_type){
        
        
        if(empty($exam_id))
            return null;
        
        $new_data = $return_data = array();
        
        $sql = 'SELECT "exam_id","school_code","student_id","subject_id","disciplin_type","score" FROM "{{point}}" WHERE "exam_id" = :exam_id ';

        $result = $this->conn->createCommand($sql);
        $result->bindParam(':exam_id',$exam_id,PDO::PARAM_STR);
        
        $data = $result->queryAll();
        if(!empty($data)){
            foreach($data as $value){
                if(!isset($new_data[$value['school_id']][$value['disciplin_type']][$value['student_id']]))
                    $new_data[$value['school_id']][$value['disciplin_type']][$value['student_id']] = 0;
                
                 $new_data[$value['school_id']][$value['disciplin_type']][$value['student_id']] += $value['score'];
            }
        }
        return $new_data;
    }
    
    //得到上线人数
    public function _getUpxian($point,$now_point){
        
        $status = 0;
    }
 
    /***
     * 在籍人数
     * 
     * @author jinyue.wang
     * @date 2013-12-16
     * @version 1.0
     */
    public function _getMemberShipObject(){
        
        $new_data = array();
        
        $sql = 'SELECT "membership_id","membership_name","membership_code","edu_type" FROM "{{membership}}"';
        $result = $this->conn->createCommand($sql);
        
        $data = $result->queryAll();
        if(!empty($data)){
            foreach($data as $value){
                $new_data[$value['membership_id']] = $value;
            }
        }
        
        return $new_data;
    }
    
    /**
     * 得到分差表信息
     * 
     * @author jinyue.wang
     * @date 2013-12-16
     * @version 1.0
     */
    public function _getPointDiff($pd_id){
        
        $data = array();
        if($pd_id>0){
            $sql = 'SELECT "pd_id","membership_name" FROM "{{points_difference}}" WHERE "pd_id" = :pd_id ';
            $result = $this->conn->createCommand($sql);
            $result->bindParam(':pd_id',$pd_id,PDO::PARAM_STR);
            
            $data = $result->queryRow();
        }
        
        return $data;
    }
    
    public function _getPointDiffArray() {

        $data = array();

        $sql = 'SELECT "pd_id","membership_name" FROM "{{points_difference}}" ORDER BY "pd_id" ASC ';
        $result = $this->conn->createCommand($sql);

        $data = $result->queryAll();
        
        return $data;
    }
 
    public function _getPointObject(){
        
        $new_data = array();
        
        $sql = 'SELECT "pd_id","membership_name" FROM "{{points_difference}}" ORDER BY "pd_id" ASC ';
        $result = $this->conn->createCommand($sql);

        $data = $result->queryAll();
        foreach($data as $value){
            $new_data[$value['pd_id']] = $value['membership_name'];
        }
        
        return $new_data;
    }
    
    /**
     * 得到考试对应的学校
     * 
     * @author jinyue.wang
     * @date 2013-12-18
     * @version 1.0
     */
    public function _getExamSchoolArray($exam_id,$zEdu,$school_ids=''){
        
        $school_array = array();
        if($exam_id>0){
            $sql = 'SELECT "school_id" FROM "{{exam_school}}" WHERE "exam_id" = :exam_id ';
            if(!empty($school_ids)){
            	$sql .= " AND \"school_id\" IN (".$school_ids.")";
            }
            $result = $this->conn->createCommand($sql);
            $result->bindParam(":exam_id", $exam_id,PDO::PARAM_STR);
            $data = $result->queryAll();
            if(!empty($data)){
                foreach($data as $value){
                    
                    $school_info = $zEdu->_getSchoolArray($value['school_id']);
                    $value['school_name'] = isset($school_info['school_name'])?$school_info['school_name']:'';
                    $value['school_code'] = isset($school_info['school_code'])?$school_info['school_code']:'';
                    
                    $school_array[] = $value;
                }
            }
        }

        return $school_array;
    }
    
    
    /**
     * （初中成绩统计）
     * 
     * @param $exam_id
     * @param $school_code
     * 
     * @date 2014-01-01
     * @version 1.0
     * @author jinyue.wang
     */
    public function _getCzNewPoint($exam_id, $school_id, $score = array(), $exam_subject,$school_list = '') {
        
        if(empty($exam_id))
            return null;
        

        $data = $new_data = array();
        

        if (!empty($school_id)) {
            $sql = 'SELECT "student_id","student_name","exam_id","school_id","school_code","school_name","disciplin_type","disciplin_code","ywf","sxf","yyf","wlf","hxf","swf","zzf","dlf","lsf","socre" FROM "{{student}}" WHERE "exam_id" = :exam_id AND "school_id" = :school_code AND "statis" = \'T\'';
            $result = $this->conn->createCommand($sql);
            $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
            $result->bindParam(':school_code',$school_id,PDO::PARAM_STR);
            $data = $result->queryAll();
        }else{
            $sql = 'SELECT "student_id","student_name","exam_id","school_id","school_code","school_name","disciplin_type","disciplin_code","ywf","sxf","yyf","wlf","hxf","swf","zzf","dlf","lsf","socre" FROM "{{student}}" WHERE "exam_id" = :exam_id AND "statis" = \'T\'';
            if(!empty($school_list)){
                $sql .= " AND \"school_id\" IN (".$school_list.") ";
        	}

        $result = $this->conn->createCommand($sql);
        $result->bindParam(':exam_id',$exam_id,PDO::PARAM_STR);
        
        $data = $result->queryAll();
        }
        if(!empty($data)){
            
            $yxl = $jgl = $dfl = 0;
            $yxa = $jga = $dfa = 0;
            foreach($data as $value){
                
                //总分统计
                foreach ($exam_subject as $sKey => $sVal){
                if (!isset($new_data[$value['school_id']]))
                    $new_data[$value['school_id']] = array();
                    if (!isset($new_data[$value['school_id']][$sKey]))
                        $new_data[$value['school_id']][$sKey] = array();
                
                    if (!isset($new_data[$value['school_id']][$sKey]['type1']))
                        $new_data[$value['school_id']][$sKey]['type1'] = 0;
                    if (!isset($new_data[$value['school_id']][$sKey]['type2']))
                        $new_data[$value['school_id']][$sKey]['type2'] = 0;
                    if (!isset($new_data[$value['school_id']][$sKey]['type3']))
                        $new_data[$value['school_id']][$sKey]['type3'] = 0;
                    
                    $pType = isset($score['subject'][$sKey]) ? $score['subject'][$sKey] : array();
                    $sub_point = isset($value[$sVal]) ? $value[$sVal] : 0;
                    $type = self::_getPointType($pType, $sub_point);
                switch ($type){
                    case 1:
                            $new_data[$value['school_id']][$sKey]['type1'] += 1;  //优秀
                            $new_data[$value['school_id']][$sKey]['type2'] += 1;  //及格
                        break;
                    case 2:
                            $new_data[$value['school_id']][$sKey]['type2'] += 1;  //及格
                        break;
                    case 3:
                            $new_data[$value['school_id']][$sKey]['type3'] += 1; //低分
                        break;
                        case 4:
                            $new_data[$value['school_id']][$sKey]['type2'] += 1;  //及格
                            $new_data[$value['school_id']][$sKey]['type3'] += 1; //低分
                            break;
                }
                
                
                if (!isset($new_data[$value['school_id']]['point'])) {
                    $new_data[$value['school_id']]['point'] = 0;
                }
                
                    $new_data[$value['school_id']]['point'] += (float) $sub_point; //总分数
                
                //单科总分
                    if (!isset($new_data[$value['school_id']]['subject'][$sKey])) {
                        $new_data[$value['school_id']]['subject'][$sKey] = 0;
                }
                    $new_data[$value['school_id']]['subject'][$sKey] += (float) $sub_point;
                
                    if (!isset($new_data[$value['school_id']]['student'][$value['student_id']])) {
                        $new_data[$value['school_id']]['student'][$value['student_id']] = 0;
                }
                    $new_data[$value['school_id']]['student'][$value['student_id']] += (float) $sub_point;   //总人数(学生总分)

                
            }
                if (!isset($new_data[$value['school_id']]['people'])) {
                    $new_data[$value['school_id']]['people'] = 0;
        }
                $new_data[$value['school_id']]['people'] += 1;   //总人数(学生总分)
            }
        }
        
        return $new_data;
    }
    /**
     * 得到学校的基本信息
     * 
     * @author jinyue.wang
     * @version 1.0
     */
    public function _getSchoolInfoList($school_id){
        $school_code = '';
        if(!empty($school_id)){
            $sql = 'SELECT "school_code" FROM "{{school}}" WHERE "school_id" IN ('.$school_id.') ';
            $result = $this->conn->createcommand($sql);
            $data = $result->queryAll();
            if(!empty($data)){
                foreach($data as $key => $value){
                    if($key==0){
                        $school_code .= ' "school_code" = \''.$value['school_code'].'\' ';
                    }else{
                        $school_code .= ' OR "school_code" = \''.$value['school_code'].'\' ';
                    }
                }
            }
        }
        return $school_code;
    }
    
    /**
     * 得到当前科目的及格率 低分率 优秀率
     * 
     * @version 1.0
     * @author jinyue.wang
     */
    public function _getPointType($pointType,$point){
        //1优秀 2及格 3低分 4 及格低分相等 5及格优秀相等
        $type = 0;
        $f = (float)$point;
        if(!empty($pointType)){
            if($f <= (float)$pointType['score_type3']){
                if($f == $pointType['score_type2']){
                    $type = 4;
                }else{
                $type = 3; //低分
                }
            }else{
                if($f >= (float)$pointType['score_type1']){
                $type = 1; //优秀
            }else{
                    if($f >= (float)$pointType['score_type2']){
                $type = 2; //及格
                    }
                }
            }
        }
        return $type;
    }
    
    /**
     *  得到考试所有学生的考试成绩
     * 
     *  @version 1.0
     *  @author jinyue.wang
     *  @date 2014-02-07
     */
    public function _getExamStuPoint($exam_id) {
        if (empty($exam_id))
            return null;
        $new_data = $return_data = array();
        $sql = 'SELECT "exam_id","school_id","student_id","subject_id","score" FROM "{{point}}" WHERE "exam_id" = :exam_id ORDER BY "score" DESC ';
        $result = $this->conn->createCommand($sql);
        $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
        $data = $result->queryAll();
        if (!empty($data)) {
            //$return_data['point'] = $data;  //得到所有人成绩
            $return_data['total'] = $num = count($data); //得到参加考试总人数
            foreach ($data as $value) {
                if (!isset($new_data[$value['school_id']]))
                    $new_data[$value['school_id']] = 0;
                $new_data[$value['school_id']] += 1;
            }
            $return_data['school'] = $new_data; //得到参加考试的每个学校总人数人数
            $people_num = (int) $this->_getPeople(0.3, $num);
            $people_num_more = (int) $this->_getPeople(0.4, $num);
            $people_num_last = (int) ($num - $people_num - $people_num_more);
            $s_num = $m_num = $l_num = array();
            $j_num = $people_num + $people_num_more;
            //得到3040的人数
            for ($i = 0; $i < $num; $i++) {
                if ($i > $j_num) {
                    if (!isset($l_num[$data[$i]['school_id']]))
                        $l_num[$data[$i]['school_id']] = 0;
                    $l_num[$data[$i]['school_id']] += 1;
                }else {
                    if ($i < $people_num) {
                        if (!isset($s_num[$data[$i]['school_id']]))
                            $s_num[$data[$i]['school_id']] = 0;
                        $s_num[$data[$i]['school_id']] += 1;
                    }else {
                        if (!isset($m_num[$data[$i]['school_id']]))
                            $m_num[$data[$i]['school_id']] = 0;
                        $m_num[$data[$i]['school_id']] += 1;
                    }
                }
            }
            ksort($s_num);
            ksort($m_num);
            ksort($l_num);
            $return_data['start_num'] = $s_num;
            $return_data['more_num'] = $m_num;
            $return_data['last_num'] = $l_num;
        }
        return $return_data;
    }
    /**
     * 计算3040人数
     * 
     * @version 1.0
     * @date 2014-02-07
     * @author jinyue.wang
     */
    public function _getPeople($bl,$count){
        $num = 0;
        if(!empty($bl) && !empty($count)){
            $num = $bl*$count;
        }
        return $num;
    }
    
    /**
     * 得到主观、客观题的汇总分数
     * 
     * @param $key 科目ywfjt
     * @param $exam_id
     * @param $student_id
     * 
     * @return num
     * @version 1.0
     * */
    /*public function _getAdditionPoint($key,$exam_id,$student_id){
    	
    	//得到客观题设置规则
    	$point = 0;
    	if($key && $exam_id>0){
    		$sql = 'SELECT "exam_id","subject_id","collect_mode","collect_questions","collect_question" FROM "{{question_sum}}" ';
    		$sql .= 'WHERE "exam_id" = :exam_id AND "collect_fields" = :cf ';
    		$result = $this->conn->createcommand($sql);
    		$result->bindParam(':exam_id',$exam_id,PDO::PARAM_STR);
    		$result->bindParam(':cf',$key,PDO::PARAM_STR);
    		$data = $result->queryRow();
    		if(!empty($data)){
    			$whereList = '';
    			if($data['collect_mode']==1){
    				$whereList = $data['collect_questions']; //题型
    			}else{
    				$whereList = $data['collect_question'];  //小题
    			}
    			$point = $this->_getSumNum($whereList,$data['collect_mode'],$data['exam_id'],$data['subject_id'],$student_id);
    		}
    	}
    	return $point;
    }*/
    
    /**
     * 汇总小题分
     * 
     * @param $flist  		//条件(小题id列表)
     * @param $type  		//类型
     * @param $exam_id  	//考试
     * @param $subject_id   //科目
     * 
     * @return point
     * 
     * @version 1.0
     * */
    /*public function _getSumNum($flist,$type,$exam_id,$subject_id,$student_id){
    	
    	$point = 0;
    	if(!empty($flist) && !empty($type) && !empty($exam_id) && !empty($subject_id) && !empty($student_id)){
    		
    		if($type==1){
    			//得到所有题型小题的总分
    		}else{
    			//得到指定题号的小题的总分
    			//$flist 题号id	
    			$sql = 'SELECT "id","number","score","school_id","sub_point" FROM "{{point}}" WHERE "exam_id" = :e_id AND "subject_id" = :sub_id AND "student_id" = :stu_id ';
    			$result = $this->conn->createCommand($sql);
    			$result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
    			$result->bindParam(':sub_id',$subject_id,PDO::PARAM_STR);
    			$result->bindParam(':stu_id',$student_id,PDO::PARAM_STR);
    			$data = $result->queryRow();	
    			if(!empty($data)){			
    				if(!empty($data['sub_point'])){				
    					$subPoint = unserialize($data['sub_point']); //得到小题序列
	    				//得到小题
	    				$point = self::_getQuestionArray($flist,$subPoint);
    				}
    			}
    		}
    	}
    	
    	return $point;
    }*/
    
    
	/**
     * 得到小题分设置规则
     * 
     * @version 10
     * @date 2014-02-13
     * */
    public function _getRuleList($key,$exam_id){
    	$point = '';
    	
    	if($key && $exam_id>0){

    		$sql = 'SELECT "exam_id","subject_id","collect_mode","collect_questions","collect_question" FROM "{{question_sum}}" ';
    		$sql .= 'WHERE "exam_id" = :exam_id AND "collect_fields" = :cf ';
    		$result = $this->conn->createcommand($sql);
    		$result->bindParam(':exam_id',$exam_id,PDO::PARAM_STR);
    		$result->bindParam(':cf',$key,PDO::PARAM_STR);
    		$data = $result->queryRow();
    		if(!empty($data)){
    			
    			if($data['collect_mode']==1){
    				$whereList = $data['collect_questions']; //题型
    			}else{
    				$whereList = $data['collect_question'];  //小题
    			}
    			
    			$point = $this->_getSumNumAll($whereList,$data['collect_mode'],$data['exam_id'],$data['subject_id'],$key);
    		}
    	}
    	return $point;
    }
    
	public function _getSumNumAll($flist,$type,$exam_id,$subject_id,$key){
    	
    	$point = 0;
    	if(!empty($flist) && !empty($type) && !empty($exam_id) && !empty($subject_id) && !empty($key)){
    		
    		//$flist 题号id	
    		$sql = 'SELECT "id","number","score","school_id","sub_point","student_id" FROM "{{point}}" WHERE "exam_id" = :e_id AND "subject_id" = :sub_id ';
    		$result = $this->conn->createCommand($sql);
    		$result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
    		$result->bindParam(':sub_id',$subject_id,PDO::PARAM_STR);
    		$data = $result->queryAll();
    		
    		if($type==1){
    			//得到所有题型小题的总分
    			if(!empty($data)){		
    				foreach ($data as $value){	
	    				if(!empty($value['sub_point'])){				
	    					$subPoint = unserialize($value['sub_point']); //得到小题序列
	    					//得到小题
		    				$point = self::_getXtArray($flist,$subPoint);
							self::_updPointXt($exam_id, $value['student_id'], $point, $key);
	    				}
    				}
    			}
    		}else{
    			//得到指定题号的小题的总分
    			if(!empty($data)){		
    				foreach ($data as $value){	
	    				if(!empty($value['sub_point'])){				
	    					$subPoint = unserialize($value['sub_point']); //得到小题序列
		    				//得到小题
		    				$point = self::_getQuestionArray($flist,$subPoint);
							self::_updPointXt($exam_id, $value['student_id'], $point, $key);
	    				}
    				}
    			}
    		}
    	}
    	
    	return $point;
    }
    
    /**
     * 
     * 设置科目规则重新生成分数
     * 
     * @version 1.0
     * @date 2014-02-13
     * 
     * */
    public function _updPointXt($exam_id,$student_id,$point,$key){
    	$status = 0;
    	
    	if(!empty($exam_id) && !empty($student_id) && !empty($point) && !empty($key)){
    		$sql = 'UPDATE "{{student}}" SET "'.$key.'" = :point WHERE "exam_id" = :e_id AND "student_id" = :stu_id ';

    		$result = $this->conn->createcommand($sql);
    		$result->bindParam(':point',$point,PDO::PARAM_STR);
    		$result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
    		$result->bindParam(':stu_id',$student_id,PDO::PARAM_STR);
    		$status = $result->execute();
    	}
    	return $status;
    }
    
   	/**
   	 * 得到小题分类型2
   	 * 
   	 * @param $id_list  汇总的小题分序列
   	 * @param $sub_point  小题分序列
   	 * 
   	 * @return num(int）
   	 * 
   	 * @version 1.0
   	 * */
    public function _getQuestionArray($id_list,$sub_point){
    	
    	$data = 0;
    	if(!empty($id_list) && !empty($sub_point)){
			$idArray = explode(',', $id_list);
			foreach($idArray as $idVal){
				$data += isset($sub_point[$idVal])?$sub_point[$idVal]:0;
			}
    	}
    	return $data;
    }
    
    /**
     * 得到小题分类型1
     * 
     * @version 1.0
     * @date 2014-02-13
     * */
    public function _getXtArray($flist,$sub_point){
    	$data = 0;
    	if(!empty($flist)){
    		$sql = 'SELECT "id","num_id" FROM "{{teacher_num}}" WHERE "num_type" = :ntype ';
    		$result = $this->conn->createcommand($sql);
    		$result->bindParam(':ntype',$flist,PDO::PARAM_STR);
    		$row = $result->queryAll();
    		if(!empty($row)){
    			foreach ($row as $value){
    				$data += isset($sub_point[$value['id']])?$sub_point[$value['id']]:0;
    			}
    		}
    	}
    	
    	return $data;
    }
    public static function getExamEduType($examID){
        $sql = "   SELECT \"edu_name\" 
                        FROM \"{{exam}}\" exam 
                        LEFT JOIN \"{{edu_type}}\"  edu_type ON exam.\"exam_edu_type\"=edu_type.\"edu_id\"
                        WHERE \"exam_id\"='{$examID}'";
        $row = Yii::app()->db->createcommand($sql)->queryRow();
        if(!$row)
            return false;
        if(strpos($row['edu_name'], "初")!==false)
            return 1;
        if(strpos($row['edu_name'], "高")!==false)
            return 2;
        return 3;
    }
    /**
     * 计算科目的达B率
     * */
    
}
?>
